package com.chhuang.tutool.db

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.util.Log
import com.chhuang.tutool.modle.SocketBean
import org.jetbrains.anko.db.ManagedSQLiteOpenHelper
import java.text.SimpleDateFormat
import java.util.*

class DatabaseHelper(ctx: Context, private var DB_VERSION: Int=CURRENT_VERSION) : ManagedSQLiteOpenHelper(ctx, DB_NAME, null, CURRENT_VERSION) {

    companion object {
        private val TAG = "DatabaseHelper"
        var DB_NAME = "TUtool.db" //数据库名称
        var CURRENT_VERSION = 1 //当前的最新版本，如有表结构变更，该版本号要加一
        private var instance: DatabaseHelper? = null
        @SuppressLint("ConstantLocale")
        private val dateFormat = SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault())
        @Synchronized
        fun Instance(ctx: Context, version: Int=0): DatabaseHelper {
            if (instance == null) {
                //如果调用时没传版本号，就使用默认的最新版本号
                instance = if (version>0) DatabaseHelper(ctx.applicationContext, version)
                else DatabaseHelper(ctx.applicationContext)
            }
            return instance!!
        }
    }

    override fun onCreate(db: SQLiteDatabase) {
        Log.d(TAG, "onCreate")
        val drop_sql = "DROP TABLE IF EXISTS ${SocketBean.TABLE_NAME};"
        Log.d(TAG, "drop_sql:" + drop_sql)
        db.execSQL(drop_sql)
        val create_sql = "CREATE TABLE IF NOT EXISTS ${SocketBean.TABLE_NAME} (" +
                "${SocketBean.PRIMARY_ID} INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + //AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
                "${SocketBean.SOCKET_TYPE} INTEGER NOT NULL," +
                "${SocketBean.IP} VARCHAR NOT NULL," +
                "${SocketBean.PORT} INTEGER NOT NULL," +
                "${SocketBean.LOCAL_PORT} INTEGER," +
                "${SocketBean.MODIFY_TIME} VARCHAR NOT NULL," +
                "${SocketBean.CREATE_TIME} VARCHAR NOT NULL);"
        Log.d(TAG, "create_sql:" + create_sql)
        db.execSQL(create_sql)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        Log.d(TAG, "onUpgrade oldVersion=$oldVersion, newVersion=$newVersion")
//        if (newVersion > 1) { //Android的ALTER命令不支持一次添加多列，只能分多次添加
//            var alter_sql = "ALTER TABLE ${SocketBean.TABLE_NAME} ADD COLUMN phone VARCHAR;"
//            Log.d(TAG, "alter_sql:" + alter_sql)
//            db.execSQL(alter_sql)
//            alter_sql = "ALTER TABLE ${SocketBean.TABLE_NAME} ADD COLUMN password VARCHAR;"
//            Log.d(TAG, "alter_sql:" + alter_sql)
//            db.execSQL(alter_sql)
//        }
    }

    fun delete(condition: String?): Int {
        var count = 0
        use {
            count = delete(SocketBean.TABLE_NAME, condition, null)
        }
        return count
    }

    fun insert(info: SocketBean): Long {
        val infoArray = mutableListOf(info)
        return insert(infoArray)
    }

    fun insert(infoArray: MutableList<SocketBean>): Long {
        var result: Long = -1
        for (info in infoArray) {
            // 不存在唯一性重复的记录，则插入新记录
            val cv = ContentValues()
            cv.put(SocketBean.SOCKET_TYPE, info.socketType)
            cv.put(SocketBean.IP, info.ip)
            cv.put(SocketBean.PORT, info.port)
            cv.put(SocketBean.LOCAL_PORT, info.loaclPort)
            cv.put(SocketBean.MODIFY_TIME, dateFormat.format(info.modifyTime))
            cv.put(SocketBean.CREATE_TIME, dateFormat.format(info.createTime))
            use {
                result = insert(SocketBean.TABLE_NAME, "", cv)
            }
            // 添加成功后返回行号，失败后返回-1
        }
        return result
    }

    @JvmOverloads
    fun update(info: SocketBean, condition: String = "${SocketBean.PRIMARY_ID}=${info.id}"): Int {
        val cv = ContentValues()
        cv.put(SocketBean.SOCKET_TYPE, info.socketType)
        cv.put(SocketBean.IP, info.ip)
        cv.put(SocketBean.PORT, info.port)
        cv.put(SocketBean.LOCAL_PORT, info.loaclPort)
        cv.put(SocketBean.MODIFY_TIME, dateFormat.format(info.modifyTime))
        cv.put(SocketBean.CREATE_TIME, dateFormat.format(info.createTime))
        var count = 0
        use {
            count = update(SocketBean.TABLE_NAME, cv, condition, null)
        }
        return count
    }

    fun query(condition: String?): List<SocketBean> {
        val sql = "select ${SocketBean.PRIMARY_ID}," +
                "${SocketBean.SOCKET_TYPE}," +
                "${SocketBean.IP}," +
                "${SocketBean.PORT}," +
                "${SocketBean.LOCAL_PORT}," +
                "${SocketBean.CREATE_TIME}," +
                "${SocketBean.MODIFY_TIME} " +
                "from ${SocketBean.TABLE_NAME} " +
                if(condition!=null)"where $condition;" else ""
        Log.d(TAG, "query sql: " + sql)
        var infoArray = mutableListOf<SocketBean>()
        use {
            val cursor = rawQuery(sql, null)
            if (cursor.moveToFirst()) {
                while (true) {
                    val info = SocketBean()
                    info.id = cursor.getInt(0)
                    info.socketType = cursor.getInt(1)
                    info.ip = cursor.getString(2)
                    info.port = cursor.getInt(3)
                    info.loaclPort = cursor.getInt(4)
                    info.modifyTime = dateFormat.parse(cursor.getString(5)) as Date
                    info.createTime = dateFormat.parse(cursor.getString(6)) as Date
                    infoArray.add(info)
                    if (cursor.isLast) {
                        break
                    }
                    cursor.moveToNext()
                }
            }
            cursor.close()
        }
        return infoArray
    }

    fun deleteById(id: Int): Boolean {
        val condition = "${SocketBean.PRIMARY_ID}=${id}"
        return delete(condition)>0
    }

    fun deleteAll(): Int = delete(null)

    fun queryAll(): List<SocketBean> = query(null)
}